import os
from sqlalchemy import create_engine, inspect, Column, String, DateTime
from sqlalchemy.orm import sessionmaker, declarative_base
from datetime import datetime
from flask import Flask, Response, stream_with_context, request, jsonify
from werkzeug.utils import secure_filename

from tq.down_data import get_1991_2020_temp_data

# pip install -i https://pypi.tuna.tsinghua.edu.cn/simple

app = Flask(__name__)
UPLOAD_FOLDER = os.getcwd()
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

Base = declarative_base()


class Article(Base):
    # 表名
    __tablename__ = 'tb_article'
    # 表结构
    id = Column(String(45), primary_key=True)
    title = Column(String(45))
    category_id = Column(String(45))
    create_time = Column(DateTime)


class Diary(Base):
    # 表名
    __tablename__ = 'tb_diary'
    # 表结构
    id = Column(String(45), primary_key=True)
    content = Column(String(45))


def to_dict(obj):
    return {c.key: getattr(obj, c.key)
            for c in inspect(obj.__class__).mapper.column_attrs}


def get_session():
    engine = create_engine('mysql+pymysql://root:123456@192.168.156.31:3308/sob', echo=False)
    # 创建DBSession类型:
    DBSession = sessionmaker(bind=engine)
    return DBSession()


# 文件下载
def file_streamer(file_path, chunk_size=1024):
    """生成器函数，用于逐块读取文件"""
    with open(file_path, 'rb') as f:
        while True:
            chunk = f.read(chunk_size)
            if not chunk:
                break
            yield chunk


@app.route('/test/tq')
def download_temp_file():
    return get_1991_2020_temp_data('57176','1,4,7,10')


@app.route('/test/download/<filename>')
def download_file(filename):
    file_path = os.path.join(UPLOAD_FOLDER, filename)  # 设置文件存储路径
    if os.path.exists(file_path):
        return Response(
            stream_with_context(file_streamer(file_path)),
            mimetype='application/octet-stream',  # 根据文件类型设置正确的MIME类型
            headers={
                'Content-Disposition': f'attachment; filename={filename}',
                'Content-Length': str(os.path.getsize(file_path))
            }
        )
    else:
        return "File not found.", 404


# 文件上传
@app.route('/test/upload', methods=['POST'])
def upload_file():
    # 检查是否有文件在请求中
    if 'file' not in request.files:
        return jsonify(error="没有文件部分"), 400

    file = request.files['file']

    # 如果用户没有选择文件，浏览器也会提交一个没有文件名的空部分
    if file.filename == '':
        return jsonify(error="没有选择文件"), 400

    if file and file.filename.endswith('.xlsx'):
        # 为文件名增加随机前缀以避免冲突
        filename = 'your_prefix_' + secure_filename(file.filename)
        # 保存文件到指定目录
        file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
        # 返回文件的存储路径
        return jsonify(success=True, filepath=os.path.join(app.config['UPLOAD_FOLDER'], filename)), 201

    return jsonify(error="不支持的文件类型"), 400


@app.route("/test/dairy")
def get_diary():
    session = get_session()
    # queryt = session.query(Diary)
    query = session.query(Article).filter(Article.create_time >= datetime(2020, 1, 1),
                                          Article.create_time <= datetime(2021, 1, 1)).order_by(Article.create_time)
    results = query.all()  # 或者使用 query.first() 来获取第一个结果
    results_dicts = [to_dict(result) for result in results]
    # 关闭会话
    session.close()
    # 返回 JSON 格式的数据
    return jsonify(data=results_dicts, code=200)


@app.route("/<string:item_id>")
def hello(item_id):
    session = get_session()
    queryt = session.query(Article).filter_by(category_id=item_id).order_by(Article.create_time)
    results = queryt.all()  # 或者使用 query.first() 来获取第一个结果
    results_dicts = [to_dict(result) for result in results]
    # 关闭会话
    session.close()
    # 返回 JSON 格式的数据
    return jsonify(data=results_dicts, code=200)


# 多参数
@app.route('/api/items')
def get_items():
    category = request.args.get('category')
    min_price = request.args.get('minPrice', type=float)
    max_price = request.args.get('maxPrice', type=float)
    sort = request.args.get('sort')
    # 使用这些参数来过滤和排序数据
    return jsonify({
        'category': category,
        'min_price': min_price,
        'max_price': max_price,
        'sort': sort,
        'data': '...'
    })


app.run()
